package com.apexedu.biz;

import com.apexedu.biz.entity.TServiceCode;
import com.apexedu.biz.entity.TServiceCodesort;
import com.apexedu.biz.entity.TServiceMenu;
import com.apexedu.framework.util.DBUtil;
import com.apexedu.framework.util.Pager;
import com.apexedu.identity.entity.TSysMenu;
import com.apexedu.portal.entity.TDb;
import com.apexedu.portal.entity.TFrontdeskNews;
import com.apexedu.portal.entity.TProblemNew;
import com.apexedu.portal.entity.TWz;
import com.apexedu.portal.util.DBMUtil;
import com.apexedu.util.StringUtil;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.util.Collections;
import java.util.List;

/**
 * 首页内容查询 将数据库信息读取出来并显示在首页
 * 
 * @author YanBing datetime 2011-8-15
 */
public class IndexPageQuery {

	private static final Log LOG = LogFactory.getLog(IndexPageQuery.class);

	/**
	 * 查看最新添加功能表的数据信息，指定资源分类下的
	 *
	 * @param type num
	 * @return
	 */
	public List<TSysMenu> getNewMenu(String type, String num, String userid) {
		String sql = " select * from  " + " (select t.*, " + " to_char((select listagg(c.zdmc) within group(order by c.wid) " + " from t_service_code c " + " where c.zdbm in (select m.serviceid from t_service_menu m where m.menuid=t.menuid) and c.zdlb='fwlb' )) as fwtype "
				+ " from t_sys_menu t  " + " where exists (select 1 from t_service_menu m where m.menuid=t.menuid and m.serviceid='"+ type +"') and t.state='1'   " + "  and  t.menuid like '000603%' ";

		if (StringUtil.isNotBlank(userid) && !"admin".equals(userid)) {
			sql = sql + "    and  exists " + "            (select 1 " + "             from t_sys_menu m  " + "             where m.bizcode = t.bizcode " + "             and m.menuid in " + "                 (select m.menuid "
					+ "                 from t_sys_role_menu m " + "                 where m.roleid in " + "                        (select ur.roleid " + "                        from t_sys_user_role ur " + "                        where ur.userid in ("
					+ "                                            select u.userid  " + "                                            from t_sys_user u  " + "                                            where u.userloginid = '" + userid + "'))))";
		}

		sql = sql + " order by t.cjrq desc) where rownum<=" + num;
		List<TSysMenu> menus = DBUtil.queryAllBeanList(sql, TSysMenu.class);
		return menus;
	}

	/**
	 * 查看热门功能表的数据信息，指定资源分类下的
	 *
	 * @param type num
	 * @return
	 */
	public List<TSysMenu> getDjsMenu(String type, String num, String userid) {
		String sql = "  select * from  " + "  (select t.*, " + "  to_char((select listagg(c.zdmc) within group(order by c.wid) " + "  from t_service_code c " + "  where c.zdbm in (select m.serviceid from t_service_menu m where m.menuid=t.menuid) and c.zdlb='fwlb' )) as fwtype "
				+ "  from t_sys_menu t " + "  where exists (select 1 from t_service_menu m where m.menuid=t.menuid and m.serviceid=?) and t.state='1' and t.menuid like '000603%' ";
		if (StringUtil.isNotBlank(userid) && !"admin".equals(userid)) {
			sql = sql
					+ " and  exists (select 1 from t_sys_menu m where m.bizcode = t.bizcode and m.menuid in (select m.menuid from t_sys_role_menu m where m.roleid in (select ur.roleid from t_sys_user_role ur where ur.userid in (select u.userid  from t_sys_user u  where u.userloginid = '"
					+ userid + "'))))";
		}
		sql = sql + "  order by t.djs desc) where rownum<=" + num;
		List<TSysMenu> menus = DBUtil.queryAllBeanList(sql, TSysMenu.class, type);
		return menus;
	}

	/**
	 * 查看功能表的数据信息，指定资源分类下的
	 *
	 * @param type_one,type_two, num
	 * @return
	 */
	public List<TSysMenu> getTypeMenu(String type_one, String type_two, String num, String userid) {
		String sql = "  select *  from " + "  (select t.*, " + "  to_char((select listagg(c.zdmc) within group(order by c.wid) " + "  from t_service_code c where c.zdbm in (select m.serviceid from t_service_menu m where m.menuid=t.menuid) and c.zdlb='fwlb' )) as fwtype "
				+ "  from t_sys_menu t  " + "  where exists (select 1 from t_service_menu m where m.menuid=t.menuid and m.serviceid=?) and  " + "        exists (select 1 from t_service_menu m where m.menuid=t.menuid and m.serviceid=?) and  "
				+ "        t.state='1' and t.menuid like '000603%' ";

		if (StringUtil.isNotBlank(userid) && !"admin".equals(userid)) {
			sql = sql + "      and  exists          " + "       (select 1           " + "       from t_sys_menu m   " + "       where m.bizcode = t.bizcode and m.menuid in (select m.menuid                  " + "       from t_sys_role_menu m "
					+ "       where m.roleid in  " + "                      (select ur.roleid  " + "                       from t_sys_user_role ur  " + "                       where ur.userid in (select u.userid  "
					+ "                       from t_sys_user u  " + "                       where u.userloginid = '" + userid + "'))))";
		}
		sql = sql + " order by t.ordernum desc )   where rownum<=" + num;

		List<TSysMenu> menus = DBUtil.queryAllBeanList(sql, TSysMenu.class, type_one, type_two);
		return menus;
	}

	public List<TWz> getNewsList(String location, String num) {

		List<TWz> list = Collections.EMPTY_LIST;
		Pager pager = new Pager();
		TDb tdb = new TDb();
		String sql_fdn = " select t.wid,t.lmmc,(select code from t_db where wid=t.code) as code,t.querysql,t.weiz,t.shuom,t.state " + " from T_FRONTDESK_NEWS t" + " where 1=1 and t.state=1 and t.weiz=? ";
		String conditions = "";
		List<TFrontdeskNews> tfrontdesknewslist = DBUtil.queryAllBeanList(sql_fdn, TFrontdeskNews.class, location);
		if (tfrontdesknewslist != null && tfrontdesknewslist.size() > 0) {
			TFrontdeskNews tfrontdesknews = tfrontdesknewslist.get(0);
			String sql_new = tfrontdesknews.getQuerysql();
			DBMUtil dbmUtil = new DBMUtil(tfrontdesknews.getCode());
			tdb = DBUtil.queryBean("select * from t_db where code=? and rownum<2", TDb.class, tfrontdesknews.getCode());
			pager.setEachPageRows(Integer.parseInt(num));
			try {
				if (tdb.getUrl().indexOf("sqlserver") != -1) {
					long c = dbmUtil.count("select count(*) from (" + sql_new + ") as t " + conditions);
					pager.setTotalRows(c);
					String sql = "select top " + pager.getEachPageRows() + " * from (" + sql_new + ") as t where 1=1  " + conditions + " and  wid not in (select top " + (pager.getCurrentPageno() - 1) * pager.getEachPageRows() + " wid from (" + sql_new
							+ ") as s where 1=1 " + conditions + " ) ";
					list = dbmUtil.queryAllBeanList(sql, TWz.class);
				} else if (tdb.getUrl().indexOf("oracle") != -1) {
					long c = dbmUtil.count("select count(*) from (" + sql_new + ")" + conditions);
					pager.setTotalRows(c);
					list = dbmUtil.queryPageBeanList("select * from (" + sql_new + ") where 1=1 " + conditions, pager, TWz.class);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 查看首页 按大类查询，小类的信息集
	 *
	 * @param type, num
	 * @return
	 */
	public List<TServiceCode> getServiceCode(String type, String num) {
		List<TServiceCode> serviceCodeList = null;
		try {
			String sql = " select * from (select * from T_SERVICE_CODE t where t.sfsy='1' and t.zdlb=? order by t.zdpx ) where rownum<=" + num;
			serviceCodeList = DBUtil.queryAllBeanList(sql, TServiceCode.class, type);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return serviceCodeList;
	}

	/**
	 * 加载业务服务分类信息
	 */
	public List<TServiceCodesort> getTservicecodesortList() {
		TServiceCodesort tservicecodesort = null;
		TServiceMenu tserviceMenu = null;
		StringBuffer sb_mene = new StringBuffer();
		String sqlCodesort = "select * from t_service_codesort t ";
		String sqlCode = "select * from t_service_code t where t.zdlb=? and t.sfsy='1'";
		String sqlmemu = "select * from t_service_menu t where t.menuid=? ";
		List<TServiceCodesort> tservicecodesortList = DBUtil.queryAllBeanList(sqlCodesort, TServiceCodesort.class);
		if (tservicecodesortList != null && tservicecodesortList.size() > 0) {
			for (int i = 0; i < tservicecodesortList.size(); i++) {
				tservicecodesort = tservicecodesortList.get(i);
				List<TServiceCode> tservicecodeList = DBUtil.queryAllBeanList(sqlCode, TServiceCode.class, tservicecodesort.getZdlb());
				tservicecodesort.setTservicecodeList(tservicecodeList);
			}
		}
		return tservicecodesortList;
	}

	/**
	 * 查看首页 按大类查询，小类的信息集
	 *
	 * @return
	 */
	public List<TSysMenu> getAllMenu() {
		String sql = " select t.*,nvl(r.unit,'暂无') as unit,nvl(r.workingtime,'暂无') as workingtime,nvl(r.workingplace,'暂无') as workingplace,nvl(r.contact,'暂无') as contact,r.ri,"
				+ " to_char((select listagg(c.zdmc) within group(order by c.wid) from t_service_code c where c.zdbm in (select m.serviceid from t_service_menu m where m.menuid=t.menuid) and c.zdlb='fwlb' )) as fwtype" + " from t_sys_menu t "
				+ " left join t_regulation_gx gx on gx.zywid=t.menuid" + " left join t_regulation r on r.wid=gx.gzwid" + " where exists (select 1 from t_service_menu m where m.menuid=t.menuid) and t.state='1'"
				+ " order by nlssort(t.menuname,'NLS_SORT=SCHINESE_PINYIN_M') ";
		List<TSysMenu> menus = DBUtil.queryAllBeanList(sql, TSysMenu.class);
		return menus;
	}

	/**
	 * 服务详情界面中其他服务采用随机排序方式展现
	 *
	 * @return
	 */
	public List<TSysMenu> getAllMenubycondition(String menuid, String num, String userid) {
		String sql = " select *  from (" + " select t.*," + " to_char((select listagg(c.zdmc) within group(order by c.wid) from t_service_code c where c.zdbm in (select m.serviceid from t_service_menu m where m.menuid=t.menuid) and c.zdlb='fwlb' )) as fwtype"
				+ " from t_sys_menu t " + " where exists (select 1 " + " from t_service_menu m " + " where m.serviceid in (select t.serviceid " + " from t_service_menu t "
				+ " where t.menuid = ? and exists (select 1 from t_service_code where zdlb='fwlb' and zdbm=t.serviceid)) and m.menuid=t.menuid)" + " and t.state='1' and t.menuid!=?";
		if (StringUtil.isNotBlank(userid) && !"admin".equals(userid)) {
			sql = sql + "   and  exists          " + "       (select 1           " + "       from t_sys_menu m   " + "       where m.bizcode = t.bizcode and m.menuid in (select m.menuid                  " + "       from t_sys_role_menu m "
					+ "       where m.roleid in  " + "                      (select ur.roleid  " + "                       from t_sys_user_role ur  " + "                       where ur.userid in (select u.userid  "
					+ "                       from t_sys_user u  " + "                       where u.userloginid = '" + userid + "'))))";
		}
		sql = sql + " order by cjrq desc " + " ) where rownum<=" + num;
		List<TSysMenu> menus = DBUtil.queryAllBeanList(sql, TSysMenu.class, menuid, menuid);
		return menus;
	}

	/**
	 * 服务详情界面中相关问题列表
	 *
	 * @return
	 */
	public List<TProblemNew> getAllProblembycondition(String menuid, String num) {
		String sql = " select *  from (" + " select t.* " + " from t_PROBLEM t " + " where t.state=1 and t.sid = ? order by t.ptime desc " + " ) where rownum<=" + num;
		List<TProblemNew> lists = DBUtil.queryAllBeanList(sql, TProblemNew.class, menuid);
		return lists;
	}
}
